﻿Imports Entities
Imports Business
Imports DataAccess
Imports Microsoft.Office.Interop

Public Class Cierre
    Private _Lista As New List(Of VentaR)
    Private _Chofer As New CHOFERES
    Private _Camion As New CAMIONES

    Private Sub Cierre_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With cCamion
            .DataSource = CamionBL.List
            .DisplayMember = "DESCRIPCION"
            .ValueMember = "PATENTE"
        End With
        With cChofer
            .DataSource = ChoferBL.List
            .DisplayMember = "NOMBRES"
            .ValueMember = "RUT"

        End With

    End Sub

    Private Sub bCancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bCancelar.Click
        Me.Dispose()
    End Sub
    Sub CargarLista()
        _Lista = VentaBL.ListCierreDiarioP(tFecha.Value, cCamion.SelectedValue, cChofer.SelectedValue)
        If IsNothing(_Lista) Then
            Util.Mensaje("No Existen Ventas Según los filtros Seleccionados")

        End If

    End Sub

    Private Sub bExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bExcel.Click
        If IsNothing(_Chofer.RUT) Then
            _Chofer = ChoferBL.SearchById(cChofer.SelectedValue)
        End If
        If IsNothing(_Camion.PATENTE) Then
            _Camion = CamionBL.SearchById(cCamion.SelectedValue)
        End If

        CargarLista()
        If IsNothing(_Lista) Then
            Util.Mensaje("No Existen Ventas Según los filtros Seleccionados")
            Exit Sub
        End If


        Dim objLibroExcel As Excel.Workbook
        Dim hoja As Excel.Worksheet

        Dim m_Excel = New Excel.Application


        'Dim OldCultureInfo As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
        'System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("es-ES")


        objLibroExcel = m_Excel.Workbooks.Add() '' Creamos una instancia del Workbooks de Excel

        hoja = CType(objLibroExcel.Worksheets(1), Excel.Worksheet)  '' Creamos una instancia de la primera hoja de trabajo de Excel

        hoja.Visible = Excel.XlSheetVisibility.xlSheetVisible

        '' Hacemos esta hoja la visible en pantalla
        '' (como seleccionamos la primera esto no es necesario
        '' si seleccionamos una diferente a la primera si lo
        '' necesitaríamos, esto lo hacemos como forma de mostrar como   cambiar de entre hojas en un documento Excel).

        hoja.Activate()

        '' Crear el encabezado de nuestro informe

        hoja.Range("B2:E2").Merge()
        hoja.Range("B2:E2").Value = "Listado Venta Diarias"
        hoja.Range("B2:E2").Font.Bold = True
        hoja.Range("B2:E2").Font.Size = 15

        With hoja.Range("B3:C3")
            .Merge()
            .Value = "Chofer:"
            .Font.Bold = True
            .Font.Size = 13
        End With
        With hoja.Range("D3:G3")
            .Merge()
            .Value = _Chofer.NOMBRES + " " + _Chofer.APELLIDOPATERNO + " " + _Chofer.APELLIDOMATERNO
            .Font.Size = 13
        End With

        With hoja.Range("B4:C4")
            .Merge()
            .Value = "Camión:"
            .Font.Bold = True
            .Font.Size = 13
        End With
        With hoja.Range("D4:g4")
            .Merge()
            .Value = _Camion.PATENTE + ", " + _Camion.DESCRIPCION.Trim
            .Font.Size = 13
        End With

        '' Crear el subencabezado de nuestro informe

        Dim objCelda As Excel.Range = hoja.Range("A6", Type.Missing)
        objCelda.Value = "Número"
        objCelda = hoja.Range("B6", Type.Missing)
        objCelda.Value = "Calle"

        objCelda = hoja.Range("C6", Type.Missing)
        objCelda.Value = "Villa"

        objCelda = hoja.Range("D6", Type.Missing)
        objCelda.Value = "Hora"

        objCelda = hoja.Range("E6", Type.Missing)
        objCelda.Value = "Forma de Pago"

        objCelda = hoja.Range("F6", Type.Missing)
        objCelda.Value = "SubTotal"
        objCelda.EntireColumn.NumberFormat = "$ ###,###,##0"

        objCelda = hoja.Range("g6", Type.Missing)
        objCelda.Value = "Descuentos"
        objCelda.EntireColumn.NumberFormat = "$ ###,###,##0"

        objCelda = hoja.Range("h6", Type.Missing)
        objCelda.Value = "Total"
        objCelda.EntireColumn.NumberFormat = "$ ###,###,##0"

        objCelda = hoja.Range("i6", Type.Missing)
        objCelda.Value = "Pagado"
        objCelda.EntireColumn.NumberFormat = "$ ###,###,##0"

        objCelda = hoja.Range("j6", Type.Missing)
        objCelda.Value = "Pediente"
        objCelda.EntireColumn.NumberFormat = "$ ###,###,##0"

        objCelda = hoja.Range("k6", Type.Missing)
        objCelda.Value = "Descripción"

        objCelda = hoja.Range("l6", Type.Missing)
        objCelda.Value = "Cantidad"

        objCelda = hoja.Range("m6", Type.Missing)
        objCelda.Value = "Valor"
        objCelda.EntireColumn.NumberFormat = "$ ###,###,##0"

        Dim fin As Boolean = False
        Dim index As Integer = 7

        For Each venta In _Lista
            With hoja
                .Cells(index, "A") = venta.Numero
                .Cells(index, "B") = venta.Calle
                .Cells(index, "C") = venta.Villa
                .Cells(index, "D") = venta.Hora
                .Cells(index, "E") = venta.FormaPago
                .Cells(index, "F") = CLng(venta.SubTotal)
                .Cells(index, "g") = CLng(venta.Descuentos)
                .Cells(index, "h") = CLng(venta.Total)
                .Cells(index, "i") = CLng(venta.Pagado)
                .Cells(index, "j") = CLng(venta.Pendiente)
            End With

            For Each detalle In venta.DetalleVenta
                hoja.Cells(index, "k") = detalle.Descripcion
                hoja.Cells(index, "l") = detalle.Cantidad
                hoja.Cells(index, "m") = detalle.Valor
                index += 1
            Next
        Next


        'calcular los totales
        Dim fPagosList = FormaPagoBL.List
        Dim TotPagado As New Dictionary(Of String, Integer)
        Dim TotPendiente As New Dictionary(Of String, Integer)
        Dim TotTotal As New Dictionary(Of String, Integer)
        Dim Cantidad As New Dictionary(Of String, Long)
        Dim totalTotal As Integer = 0
        For Each rec In _Lista
            For Each fpago In fPagosList
                If rec.FormaPago.Equals(fpago.DESCRIPCION) Then
                    If TotPagado.ContainsKey(fpago.DESCRIPCION) Then
                        TotPagado.Item(fpago.DESCRIPCION) += rec.Pagado

                    Else
                        TotPagado.Add(fpago.DESCRIPCION, 0)
                        TotPagado.Item(fpago.DESCRIPCION) += rec.Pagado

                    End If

                    If TotPendiente.ContainsKey(fpago.DESCRIPCION) Then
                        TotPendiente.Item(fpago.DESCRIPCION) += rec.Pendiente
                    Else
                        TotPendiente.Add(fpago.DESCRIPCION, 0)
                        TotPendiente.Item(fpago.DESCRIPCION) += rec.Pendiente
                    End If

                    If TotTotal.ContainsKey(fpago.DESCRIPCION) Then
                        TotTotal.Item(fpago.DESCRIPCION) += rec.Total
                    Else
                        TotTotal.Add(fpago.DESCRIPCION, 0)
                        TotTotal.Item(fpago.DESCRIPCION) += rec.Total
                    End If
                    If Cantidad.ContainsKey(fpago.DESCRIPCION) Then
                        Cantidad.Item(fpago.DESCRIPCION) += 1
                    Else
                        Cantidad.Add(fpago.DESCRIPCION, 0)
                        Cantidad.Item(fpago.DESCRIPCION) += 1
                    End If
                End If
            Next
            totalTotal += rec.Total
        Next
        Dim indexOld = index
        index += 1
        hoja.Cells(index, "f") = "Monto Total"
        hoja.Cells(index, "g") = "Monto Pagado"
        hoja.Cells(index, "h") = "Monto Pendiente"
        hoja.Cells(index, "i") = "Cantidad de Pagos"
        Dim indexI = index
        index += 1
        Dim totCan As Long
        For Each tot In TotPagado
            hoja.Cells(index, "e") = tot.Key
            hoja.Cells(index, "g") = tot.Value
            For Each tota In TotPendiente
                If tot.Key = tota.Key Then
                    hoja.Cells(index, "h") = tota.Value
                End If
            Next
            For Each toto In TotTotal
                If tot.Key = toto.Key Then
                    hoja.Cells(index, "f") = toto.Value
                End If
            Next
            For Each cant In Cantidad
                If tot.Key = cant.Key Then
                    hoja.Cells(index, "i") = cant.Value
                    totCan += cant.Value
                End If
            Next
            index += 1
        Next
        index += 1

        hoja.Cells(index, "e") = "Total Ventas"
        hoja.Cells(index, "f") = totalTotal

        Dim rango As Excel.Range = hoja.Range("i" & (indexOld - 1).ToString & ":i" + index.ToString)
        rango.NumberFormat = "###,##0"

        Dim objRango As Excel.Range = hoja.Range("A6:m" & (indexOld - 1).ToString)
        objRango.Select()
        objRango.Columns.AutoFit()
        objRango.AutoFormat(CType(11, Excel.XlRangeAutoFormat), Alignment:=False)

        Dim objRango2 As Excel.Range = hoja.Range("e" + indexI.ToString + ":i" & (index).ToString)
        objRango2.Select()
        objRango2.Columns.AutoFit()
        objRango2.AutoFormat(CType(11, Excel.XlRangeAutoFormat), Alignment:=False)
       





        m_Excel.Visible = True
        hoja.Name = "Venta Diaria " + _Chofer.NOMBRES
        'Try
        '    objLibroExcel.SaveAs("c:\Venta Diaria.xlsx")
        'Catch ex As Exception

        'End Try

        'm_Excel.Quit()
        m_Excel = Nothing
    End Sub
End Class